const express = require('express')
const cors = require('cors')
const sqlite3 = require('sqlite3').verbose()
const path = require('path')
const multer = require('multer')
const fs = require('fs')

const app = express()
const PORT = process.env.PORT || 3001

// 中间件
app.use(cors())
app.use(express.json())
app.use(express.urlencoded({ extended: true }))

// 创建数据库
const db = new sqlite3.Database('music.db')

// 创建表
db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS songs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    artist TEXT,
    album TEXT,
    duration INTEGER,
    file_path TEXT UNIQUE,
    cover_path TEXT,
    lyrics TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`)

  db.run(`CREATE TABLE IF NOT EXISTS playlists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    cover_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`)

  db.run(`CREATE TABLE IF NOT EXISTS playlist_songs (
    playlist_id INTEGER,
    song_id INTEGER,
    position INTEGER,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (playlist_id, song_id),
    FOREIGN KEY (playlist_id) REFERENCES playlists(id),
    FOREIGN KEY (song_id) REFERENCES songs(id)
  )`)
})

// 搜索API
app.get('/api/search', (req, res) => {
  const { q, type = 'all', limit = 20, offset = 0 } = req.query
  
  let query = ''
  let params = []
  
  if (type === 'songs') {
    query = `SELECT * FROM songs WHERE title LIKE ? OR artist LIKE ? LIMIT ? OFFSET ?`
    params = [`%${q}%`, `%${q}%`, limit, offset]
  } else if (type === 'artists') {
    query = `SELECT DISTINCT artist FROM songs WHERE artist LIKE ? LIMIT ? OFFSET ?`
    params = [`%${q}%`, limit, offset]
  } else {
    query = `SELECT * FROM songs WHERE title LIKE ? OR artist LIKE ? OR album LIKE ? LIMIT ? OFFSET ?`
    params = [`%${q}%`, `%${q}%`, `%${q}%`, limit, offset]
  }

  db.all(query, params, (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else {
      res.json({ results: rows })
    }
  })
})

// 获取本地音乐
app.get('/api/local-songs', (req, res) => {
  db.all('SELECT * FROM songs ORDER BY created_at DESC', (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else {
      res.json({ songs: rows })
    }
  })
})

// 添加本地音乐
app.post('/api/songs', (req, res) => {
  const { title, artist, album, duration, file_path, cover_path, lyrics } = req.body
  
  db.run(
    `INSERT INTO songs (title, artist, album, duration, file_path, cover_path, lyrics) 
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [title, artist, album, duration, file_path, cover_path, lyrics],
    function(err) {
      if (err) {
        res.status(500).json({ error: err.message })
      } else {
        res.json({ id: this.lastID })
      }
    }
  )
})

// 获取歌单
app.get('/api/playlists', (req, res) => {
  db.all('SELECT * FROM playlists ORDER BY created_at DESC', (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else {
      res.json({ playlists: rows })
    }
  })
})

// 创建歌单
app.post('/api/playlists', (req, res) => {
  const { name, description, cover_path } = req.body
  
  db.run(
    'INSERT INTO playlists (name, description, cover_path) VALUES (?, ?, ?)',
    [name, description, cover_path],
    function(err) {
      if (err) {
        res.status(500).json({ error: err.message })
      } else {
        res.json({ id: this.lastID })
      }
    }
  )
})

// 获取歌单详情
app.get('/api/playlists/:id', (req, res) => {
  const playlistId = req.params.id
  
  db.all(
    `SELECT s.* FROM songs s 
     JOIN playlist_songs ps ON s.id = ps.song_id 
     WHERE ps.playlist_id = ? 
     ORDER BY ps.position`,
    [playlistId],
    (err, rows) => {
      if (err) {
        res.status(500).json({ error: err.message })
      } else {
        res.json({ songs: rows })
      }
    }
  )
})

// 添加歌曲到歌单
app.post('/api/playlists/:id/songs', (req, res) => {
  const playlistId = req.params.id
  const { song_id } = req.body
  
  db.get(
    'SELECT MAX(position) as max_pos FROM playlist_songs WHERE playlist_id = ?',
    [playlistId],
    (err, row) => {
      if (err) {
        res.status(500).json({ error: err.message })
      } else {
        const position = (row?.max_pos || 0) + 1
        db.run(
          'INSERT INTO playlist_songs (playlist_id, song_id, position) VALUES (?, ?, ?)',
          [playlistId, song_id, position],
          function(err) {
            if (err) {
              res.status(500).json({ error: err.message })
            } else {
              res.json({ success: true })
            }
          }
        )
      }
    }
  )
})

// 启动服务器
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`)
})
